package com.henry.veterinary;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class AnimalHospitalDatabase implements AnimalHospital {

    private Connection connection;

    private boolean connected = false;

    public AnimalHospitalDatabase() {
        this.connected = false;
    }

    public void connect(String server, int port, String username, String password) {
        String url = "jdbc:mysql://" + server + ":" + port + "/animal_hospital?useSSL=false";
        try {
            connection = DriverManager.getConnection(url, username, password);
            connected = true;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    public boolean isConnected() {
        return connected;
    }

    private List<Animal> queryAnimals(String query) {
        List<Animal> animals = new ArrayList<>();
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                Animal animal = new Animal(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6)
                );
                animals.add(animal);
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return animals;
    }

    @Override
    public List<Animal> getAllAnimals() {
        String queryForAllAnimals = "SELECT * FROM `animal_hospital`.`animal`";
        return queryAnimals(queryForAllAnimals);
    }

    @Override
    public List<Animal> getAnimalsByOwnerID(String ownerID){
        String query = "SELECT * FROM `animal_hospital`.`animal` WHERE `animal_owner_id` = '" + ownerID + "'";
        return queryAnimals(query);
    }

    @Override
    public Animal getAnimalByID(String animalID) {
        String query = "SELECT * FROM `animal_hospital`.`animal` WHERE `animal_id` = '" + animalID + "'";
        List<Animal> result = queryAnimals(query);
        if (result.isEmpty()) return null;
        else return result.get(0);
    }

    private List<Owner> queryOwners(String query) {
        List<Owner> owners = new ArrayList<>();

        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                Owner owner = new Owner(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)
                );
                owners.add(owner);
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return owners;
    }

    @Override
    public List<Owner> getAllOwners() {
        String queryForAllOwners = "SELECT * FROM `animal_hospital`.`owner`";
        return queryOwners(queryForAllOwners);
    }

    @Override
    public List<Owner> getOwnerByContact(String ownerContact) {
        String query = "SELECT * FROM `animal_hospital`.`owner` WHERE `owner_contact` LIKE '" + ownerContact + "'";
        return queryOwners(query);
    }

    @Override
    public List<Owner> getOwnerByName(String ownerName) {
        String query = "SELECT * FROM `animal_hospital`.`owner` WHERE `owner_name` LIKE '" + ownerName + "'";
        return queryOwners(query);
    }

    @Override
    public void addOwner(Owner owner) {
        String insertIntoOwner = "INSERT INTO `animal_hospital`.`owner` ";
        String values = "VALUES (" +
                "'" + owner.getID() + "', " +
                "'" + owner.getName() + "', " +
                "'" + owner.getContact() + "', " +
                "'" + owner.getAddress() + "')";
        try {
            Statement st = connection.createStatement();
            st.executeQuery(insertIntoOwner + values);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    @Override
    public void addAnimal(Animal animal) {
        String insertIntoOwner = "INSERT INTO `animal_hospital`.`animal` ";
        String values = "VALUES (" +
                "'" + animal.getID() + "', " +
                "'" + animal.getOwnerID() + "', " +
                "'" + animal.getName() + "', " +
                "'" + animal.getType() + "', " +
                "'" + animal.getAge() + "', " +
                "'" + animal.getSex() + "')";
        try {
            Statement st = connection.createStatement();
            st.executeQuery(insertIntoOwner + values);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    @Override
    public boolean addRegistration(Registration registration) {
        String insertIntoORegistration = "INSERT INTO `animal_hospital`.`registration` ";
        String values = "VALUES (" +
                "'" + registration.getRegistrationID() + "', " +
                "'" + registration.getTimeString() + "', " +
                "'" + registration.getAnimalID() + "', " +
                "'" + registration.getRecord() + "', " +
                "'" + registration.getDoctorID() + "')";
        try {
            Statement st = connection.createStatement();
            return st.executeUpdate(insertIntoORegistration + values) != 0;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return false;
    }

    private List<Registration> queryRegistration(String query) {
        List<Registration> regs = new ArrayList<>();

        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                Registration registration = new Registration(
                        rs.getInt(1),
                        LocalDateTime.parse(rs.getString(2).replace(" ", "T")),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5)
                );
                regs.add(registration);
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return regs;
    }

    @Override
    public List<Registration> getRegistrationOfAnimal(String animalID) {
        String query = "SELECT * FROM `animal_hospital`.`registration` WHERE `animal_id` = '" + animalID + "'";
        return queryRegistration(query);
    }

    @Override
    public List<Registration> getPatientsOfToday() {
        String query = "SELECT * FROM `animal_hospital`.`registration` WHERE `registration_time` > current_date()";
        return queryRegistration(query);
    }

    @Override
    public long getRegistrationCount() {
        Statement st = null;
        try {
            st = connection.createStatement();
            ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM `animal_hospital`.`registration`");
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return 0;
    }

    public void close() {
        try {
            connection.close();
            connected = false;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }
}
